package dao.impl;

import bean.Contact;
import bean.Page;
import dao.ContactDAO;
import tool.ObjectConverter;

import javax.naming.NamingException;

import org.eclipse.jdt.annotation.NonNull;

import java.sql.*;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.Vector;

public class ContactDAOImpl implements ContactDAO {
    private Connection conn;

    public ContactDAOImpl(@NonNull Connection conn) {
        this.conn = conn;
    }
    
    private static StringBuilder appendIfContactNameNonNull(@NonNull StringBuilder builder, String contactName) {
        if (Objects.nonNull(contactName)) {
            int length = contactName.length();
            if (length > 0 && length <= Contact.MAX_CONTACT_NAME_LENGTH) {
                builder.append(" AND C.CONTACT_NAME LIKE '%" + contactName + "%'");
            }
        }
        return builder;
    }
    
    private static StringBuilder appendIfSexIsknown(@NonNull StringBuilder builder, int sex) {
        sex = Contact.sex(sex);
        return sex != Contact.UNKNOWN_SEX ? builder.append(" AND C.SEX=" + sex) : builder;
    }
    
    private static StringBuilder appendIfBirthdateNonNull(@NonNull StringBuilder builder, Date birthdate) {
        if (Objects.nonNull(birthdate)) {
            String text = ObjectConverter.formatDateToText(birthdate);
            return builder.append(" AND C.BIRTHDATE=TO_DATE('" + text + "','YYYY-MM-DD')");
        }
        return builder;
    }
    
    private static StringBuilder appendIfTelephoneNonNull(@NonNull StringBuilder builder, String telephone) {
        if (Objects.nonNull(telephone)) {
            int length = telephone.length();
            if (length > 0 && length <= Contact.MAX_TELEPHONE_LENGTH) {
                builder.append(" AND C.TELEPHONE LIKE '%" + telephone + "%'");
            }
        }
        return builder;
    }
    
    private static StringBuilder appendIfGroupIdGreatThanZero(@NonNull StringBuilder builder, int groupId) {
        return builder.append(" AND C.GROUP_ID=" + groupId);
    }

    @Override
    public Page selectByContact(@NonNull Contact temporary, int size, int serial)
            throws SQLException, NamingException {
        StringBuilder queryCountStatementBuilder = new StringBuilder();
        StringBuilder queryStatementBuilder = new StringBuilder();
        int groupId = temporary.getGroupId(), accountId = temporary.getAccountId(), recordsCount;
        Page page = size < Page.DEFAULT_SIZE ? new Page() : new Page(size);
        // 使用count函数查询符合指定条件的CONTACT_ID的数量，即联系人的数量
        // 查询所有符合条件的行的数量
        String queryCountStatement = "SELECT COUNT(*) FROM CONTACTS C,GROUPS G WHERE C.GROUP_ID=G.GROUP_ID AND C.ACCOUNT_ID=" + accountId;
        queryCountStatementBuilder.append(queryCountStatement);
        if (groupId > 0) {
            appendIfGroupIdGreatThanZero(queryCountStatementBuilder, groupId);
            appendIfGroupIdGreatThanZero(queryStatementBuilder, groupId);
        }
        String contactName = temporary.getContactName();
        appendIfContactNameNonNull(queryCountStatementBuilder, contactName);
        appendIfContactNameNonNull(queryStatementBuilder, contactName);
        int sex = temporary.getSex();
        appendIfSexIsknown(queryCountStatementBuilder, sex);
        appendIfSexIsknown(queryStatementBuilder, sex);
        Date birthdate = temporary.getBirthdate();
        appendIfBirthdateNonNull(queryCountStatementBuilder, birthdate);
        appendIfBirthdateNonNull(queryStatementBuilder, birthdate);
        String telephone = temporary.getTelephone();
        appendIfTelephoneNonNull(queryCountStatementBuilder, telephone);
        appendIfTelephoneNonNull(queryStatementBuilder, telephone);
        Statement s = conn.createStatement();
        // 查询所有满足WHERE条件的记录数目
        ResultSet rs = s.executeQuery(queryCountStatementBuilder.toString());
        recordsCount = rs.next() ? rs.getInt(1) : 0;
        rs.close();
        // 获取所有分页的总记录数目
        page.setRecordsCount(recordsCount);
        page.setSerial(serial);
        serial = page.getSerial();
        String queryStatementPrefix = "SELECT * FROM"
            + " (SELECT C.*,ROWNUM R FROM CONTACTS C,GROUPS G"
            + " WHERE ROWNUM<=" + page.getSize() * serial
            + " AND C.GROUP_ID=G.GROUP_ID"
            + " AND C.ACCOUNT_ID=" + accountId;
        queryStatementBuilder.insert(0, queryStatementPrefix);
        String queryStatementSuffix = " ORDER BY CONTACT_ID ASC) TEMP"
            + " WHERE TEMP.R>" + page.getSize() * (serial - 1);
        String sql = queryStatementBuilder.append(queryStatementSuffix).toString();
        rs = s.executeQuery(sql);
        List<Contact> contacts = new Vector<>(size);
        while (rs.next()) {
            temporary = new Contact();
            temporary.setContactId(rs.getInt("CONTACT_ID"));
            temporary.setContactName(rs.getString("CONTACT_NAME"));
            temporary.setSex(rs.getInt("SEX"));
            temporary.setBirthdate(rs.getDate("BIRTHDATE"));
            temporary.setTelephone(rs.getString("TELEPHONE"));
            temporary.setGroupId(rs.getInt("GROUP_ID"));
            contacts.add(temporary);
        }
        page.setRecords(contacts);
        rs.close();
        s.close();
        return page;
    }

    @Override
    public Contact selectById(@NonNull Contact temporary) throws SQLException, NamingException {
        Contact contact = null;
        String sql = "SELECT * FROM CONTACTS WHERE CONTACT_ID=? AND ACCOUNT_ID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, temporary.getContactId());
        ps.setInt(2, temporary.getAccountId());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            contact = new Contact();
            contact.setContactId(rs.getInt("CONTACT_ID"));
            contact.setContactName(rs.getString("CONTACT_NAME"));
            contact.setSex(rs.getInt("SEX"));
            contact.setBirthdate(rs.getDate("BIRTHDATE"));
            contact.setTelephone(rs.getString("TELEPHONE"));
            contact.setGroupId(rs.getInt("GROUP_ID"));
        }
        rs.close();
        ps.close();
        return contact;
    }

    @Override
    public void deleteByIds(@NonNull String[] contactIds, int accountId) throws SQLException, NamingException {
        StringBuilder queryStatementBuilder = new StringBuilder("DELETE FROM CONTACTS WHERE CONTACT_ID IN (");
        for (int index = 0; index < contactIds.length; index++) {
            if (index < contactIds.length - 1) {
                queryStatementBuilder.append(contactIds[index] + ",");
            } else {
                queryStatementBuilder.append(contactIds[index] + ")");
            }
        }
        queryStatementBuilder.append(" AND ACCOUNT_ID=" + accountId);
        Statement s = conn.createStatement();
        s.executeUpdate(queryStatementBuilder.toString());
        s.close();
    }

    // 通过群组编号查找联系人编号
    @Override
    public boolean selectIdByGroupId(@NonNull Contact temporary) throws SQLException, NamingException {
        String sql = "SELECT COUNT(CONTACT_ID) FROM CONTACTS WHERE GROUP_ID=? AND ACCOUNT_ID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, temporary.getGroupId());
        ps.setInt(2, temporary.getAccountId());
        ResultSet rs = ps.executeQuery();
        boolean state = rs.next();
        rs.close();
        ps.close();
        return state;
    }

    @Override
    public boolean insert(@NonNull Contact temporary) throws SQLException, NamingException {
        int groupId = temporary.getGroupId(), accountId = temporary.getAccountId();
        String sql = "INSERT INTO CONTACTS VALUES (NULL,?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, temporary.getContactName());
        int sex = Contact.sex(temporary.getSex());
        ps.setInt(2, sex);
        if (Objects.isNull(temporary.getBirthdate())) {
            ps.setDate(3, null);
        } else {
            ps.setDate(3, new java.sql.Date(temporary.getBirthdate().getTime()));
        }
        ps.setString(4, temporary.getTelephone());
        ps.setInt(5, groupId);
        ps.setInt(6, accountId);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    @Override
    public boolean updateById(@NonNull Contact temporary) throws SQLException, NamingException {
        // 更新联系人
        String sql = "UPDATE CONTACTS SET";
        sql += " CONTACT_NAME=?,SEX=?,BIRTHDATE=?,TELEPHONE=?,GROUP_ID=?";
        sql += " WHERE CONTACT_ID=? AND ACCOUNT_ID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, temporary.getContactName());
        int sex = Contact.sex(temporary.getSex());
        ps.setInt(2, sex);
        if (Objects.isNull(temporary.getBirthdate())) {
            ps.setDate(3, null);
        } else {
            ps.setDate(3, new java.sql.Date(temporary.getBirthdate().getTime()));
        }
        ps.setString(4, temporary.getTelephone());
        ps.setInt(5, temporary.getGroupId());
        ps.setInt(6, temporary.getContactId());
        ps.setInt(7, temporary.getAccountId());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    @Override
    public boolean deleteById(@NonNull Contact temporary) throws SQLException, NamingException {
        String sql = "DELETE FROM CONTACTS WHERE CONTACT_ID=? AND ACCOUNT_ID=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, temporary.getContactId());
        ps.setInt(2, temporary.getAccountId());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
